package com.zues.samples.util;

import java.io.InputStream;
import java.sql.Date;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zues.samples.service.modelVO.UserVO;

public class ExcelUtils {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = "xlsx";

	@SuppressWarnings("static-access")
	public static String getCellValue(Cell cell) {
		// 如果为null 则 空
		if (cell == null) {
			return "";
		}
		// 如果为布尔值 则输出布尔，为精度小数点则输出double，字符串则输出字符
		if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/**
	 * 根据Excel后缀穿件 workbook
	 * 
	 * @param is
	 * @param postfix
	 * @return
	 */
	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfWorkbook = null;
		try {
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				hssfWorkbook = new HSSFWorkbook(is);
			} else if (OFFICE_EXCEL_2007_POSTFIX.equals(postfix)) {
				hssfWorkbook = new XSSFWorkbook(is);
			}
		} catch (Exception e) {
			if (e instanceof NotOLE2FileException) {
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}

	public static List<UserVO> getStudentListByExcel(Workbook wk) {
		final String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 0, GENDER_FEMALE_INTEGER = 1;
		final String ROLE_MANAGE_STRING= "管理员", ROLE_TEACHER_STRING = "教师", ROLE_STUDENT_STRING = "学生";
		final Integer ROLE_MANAGE_INTEGER = 0, ROLE_TEACHER_INTEGER = 1,ROLE_STUDENT_INTEGER = 2;
		List<UserVO> resultList = new ArrayList<UserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNubmer = 0;
		int maxRowNumber = sheet.getLastRowNum();
		UserVO vo = null;
		for (int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++) {
			Row row = sheet.getRow(currentRowNumber);
			maxCellNubmer = row.getLastCellNum();
			vo = new UserVO();
			for (int currentCellNumber = 0; currentCellNumber <= maxCellNubmer; currentCellNumber++) {
				Cell cell = row.getCell(currentCellNumber);
				switch (currentCellNumber) {
				case 0:
					// 获取学号
					vo.setUserID((String) getCellValue(cell));
					break;
				case 1:
					// 获取名字
					vo.setUserName((String) getCellValue(cell));
					break;
				case 2:
					//获取性别
					String gender = (String) getCellValue(cell);
					if (GENDER_MALE_STRING.equals(gender)) {
						vo.setGender(GENDER_MALE_INTEGER);
					} else if (GENDER_FEMALE_STRING.equals(gender)) {
						vo.setGender(GENDER_FEMALE_INTEGER);
					} else {
						vo.setGender(0);
					}
					break;
				case 3:
					// 获取生日
					// 获得SimpleDateFormat类，我们转换为yyyy-MM-dd的时间格式
					//SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
					//try {
					    String temp =getCellValue(cell);
						vo.setBirthday(Date.valueOf(temp));
						break;
					// } catch (ParseException e) {
					// e.printStackTrace();
					// }
				case 4:
					// 获取电话
					vo.setTel((String) getCellValue(cell));
					break;
				case 5:
					// 获取地址
					vo.setAddress((String) getCellValue(cell));
					break;
				case 6:
					// 获取获取邮箱
					vo.setEmail((String) getCellValue(cell));
					break;
				case 7:
					// 获取密码
					String decodingPassword = ((String) getCellValue(cell));
					//vo.setPassword(MD5Utils.GetMD5Code(decodingPassword));
					vo.setPassword(decodingPassword);
					break;
				case 8:
					String role = (String) getCellValue(cell);
					if (ROLE_MANAGE_STRING.equals(role)) {
						vo.setRole(ROLE_MANAGE_INTEGER);
					} else if (ROLE_TEACHER_STRING.equals(role)) {
						vo.setRole(ROLE_TEACHER_INTEGER);
					} else {
						vo.setRole(ROLE_STUDENT_INTEGER);
					}
					break;
				}
			}
			resultList.add(vo);
		}
		return resultList;

	}

}
